package com.edu.hre.db.sql.admin;

import java.io.Serializable;
import java.math.BigInteger;
import java.util.ArrayList;
import java.util.List;

import com.edu.hre.common.utils.CommonUtils;
import com.edu.hre.db.pojo.HreProject;
import com.edu.hre.db.service.BaseService;
import com.edu.hre.db.utils.Compare;
import com.edu.hre.struts.form.admin.ProjectForm;
import com.edu.hre.struts.form.admin.ProjectListForm;

public class ProjectDAOService implements Serializable {
	
	/**
	 * 
	 */
	private static final long serialVersionUID = 3394301416150421253L;
	public static final String BEAN_NAME = "projectDAOService";
	private BaseService baseService  = null;
	 
	public void setBaseService(BaseService baseService) {
		this.baseService = baseService;
	} 

	public ArrayList<ProjectForm> getProjectListInfo(ProjectListForm form, int limit, int offset){

		ArrayList<ProjectForm> listResult = new ArrayList<ProjectForm>();
		String sql = "SELECT pro.project_id AS col1," +
					" cus.name AS col2, " +
					" pro.name AS col3, " +
					" pro.description AS col4 " +

					" FROM hre_project AS pro," +
					" hre_customer AS cus" +

					" WHERE pro.customer_id = cus.customer_id" +
					" AND pro.is_deleted != 1";
		
		if (form.getCustomerName() != null && !form.getCustomerName().equals("")) {
			sql += " AND cus.name LIKE '%" + form.getCustomerName() + "%' ";
		}

		if (form.getProject() != null && !form.getProject().equals("")) {
			sql += " AND pro.name LIKE '%" + form.getProject() + "%' ";
		}
		
		sql  += " ORDER BY pro.date_created DESC";

		List <Object[]> listObject = baseService.listArrayObject(sql, limit, offset);
		for(int i = 0; listObject != null && i < listObject.size(); i++){

			try {

				Object[] objs = listObject.get(i);
				ProjectForm projectForm = new ProjectForm();

				projectForm.setId(CommonUtils.normalizeObjectToString(objs[1], "0"));
				projectForm.setCustomerName(CommonUtils.normalizeObjectToString(objs[2], ""));
				projectForm.setProject(CommonUtils.normalizeObjectToString(objs[3], ""));
				
				String projectAdmins = getProjectAdmins(projectForm.getId());
				projectForm.setProjectAdmins(projectAdmins);
				projectForm.setDescription(CommonUtils.normalizeObjectToString(objs[4], ""));

				if (form.getProjectAdmin() != null 
						&& !projectAdmins.toLowerCase().contains(form.getProjectAdmin().toLowerCase())) {
					continue;
				}

				listResult.add(projectForm);
			} catch (Exception e) {
				CommonUtils.println(e.getMessage());
			}
		}

		return listResult;
	}
	
	private String getProjectAdmins (String projectId) {
		
		String sql = "SELECT CONCAT(emp.emp_lastname, ' ', emp.emp_middle_name, ' ', emp.emp_firstname) AS col1" +

					" FROM hre_project_admin AS pad," +
					" hre_project AS pro, " +
					" hre_employee AS emp " +

					" WHERE pad.project_id = pro.project_id" +
					" AND pad.emp_number = emp.emp_number" +
					" AND pro.project_id = ?";
		
		List <Object[]> listObject = baseService.listArrayObject(sql, 0, 0, projectId);

		String result = "";
		for(int i = 0; listObject != null && i < listObject.size(); i++){
			
			try {

				Object[] objs = listObject.get(i);
				result += CommonUtils.normalizeObjectToString(objs[1], "") + ", ";
			} catch (Exception e) {
				CommonUtils.println(e.getMessage());
			}
		}
		
		// Remove the last comma and space if they exist.
		if (result.length() < 2) {
			return result;
		}

		return result.substring(0, result.length() - 2);
	}

	public int countProjects(ProjectListForm form) {

		String sql = "SELECT COUNT(*) AS col1" +
					
					" FROM hre_project AS pro," +
					" hre_customer AS cus" +
					
					" WHERE pro.customer_id = cus.customer_id" +
					" AND pro.is_deleted != 1";
		
		if (form.getCustomerName() != null && !form.getCustomerName().equals("")) {
			sql += " AND cus.name LIKE '%" + form.getCustomerName() + "%' ";
		}
		if (form.getProject() != null && !form.getProject().equals("")) {
			sql += " AND pro.name LIKE '%" + form.getProject() + "%' ";
		}
		
		try {
			List <Object[]> listObject = baseService.listArrayObject(sql, 0, 0);
			Object o [] = listObject.get(0);
			return ((BigInteger) o[1]).intValue();
		} catch (Exception e) {
			CommonUtils.println(e.getMessage());
		}

		return 0;
	}

	public void deleteProjects(ProjectListForm form){

		ArrayList<ProjectForm> info = (ArrayList<ProjectForm>) form.getAllProjects();

		for (int i = 0; i < info.size(); i++) {

			try {

				ProjectForm project = info.get(i);
				if (project.isSelected()) {

					HreProject selected = baseService.getFirstPojo("projectId", Compare.EQUAL, Integer.parseInt(project.getId()), null, null, 0, HreProject.class);
					selected.setIsDeleted(true);
					baseService.saveOrUpdate(selected);
				}
			} catch (Exception e) {
				CommonUtils.println(e.getMessage());
			}
		}
	}
}
